package cs5226.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;


import cs5226.batchjob.QueryUtil;
import cs5226.model.AwrSetting;
import cs5226.model.BaselineBean;
import cs5226.model.ThresholdBean;


public class ConfigurationDAO {

	public static AwrSetting getAwrSetting()
			throws SQLException {
		String query = "SELECT DBID, TOPNSQL, EXTRACT (DAY FROM SNAP_INTERVAL), EXTRACT (HOUR FROM SNAP_INTERVAL), EXTRACT (MINUTE FROM SNAP_INTERVAL)," +
				" EXTRACT (DAY FROM RETENTION), EXTRACT (HOUR FROM RETENTION), EXTRACT (MINUTE FROM RETENTION) FROM DBA_HIST_WR_CONTROL ";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();

			if (rs.next()) {
				int i = 1;
				String dbid = rs.getString(i++);
				String topnsql = rs.getString(i++);
				int interval = rs.getInt(i++)*24*60 + rs.getInt(i++)*60 + rs.getInt(i++);
				int retention = rs.getInt(i++)*24*60 + rs.getInt(i++)*60 + rs.getInt(i++);
				
				return new AwrSetting(dbid, interval, retention, topnsql);
			}
		} 		
		finally {
			DBUtil.close(rs, stmt, conn);
		}

		return null;
	}
	
	public static void modifyAwrSetting(int interval, int retention)
			throws SQLException {
		String query = "{ call DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (INTERVAL => ?, RETENTION => ?) } ";
		Connection conn = null;
		CallableStatement stmt = null;
				
		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareCall(query);
			stmt.setInt(1, interval);
			stmt.setInt(2, retention);
			
			stmt.executeUpdate();

		} finally {
			DBUtil.close(null, stmt, conn);
		}
	}
	
	public static ArrayList<ThresholdBean> getThresholds() throws SQLException {
		String query = "SELECT PARAM_ID, PARAM_NAME, PARAM_MIN, PARAM_UNHEALTHY, PARAM_CRITICAL, PARAM_MAX, PARAM_UNIT  FROM THRESHOLD_PARAM";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<ThresholdBean> aList = new ArrayList<ThresholdBean>();

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();

			while(rs.next()) {
				ThresholdBean abean = new ThresholdBean();
				
				abean.setParamID(rs.getString(1));
				abean.setParamName(rs.getString(2));
				abean.setMin(rs.getDouble(3));
				abean.setUnhealthy(rs.getDouble(4));
				abean.setCritical(rs.getDouble(5));
				abean.setMax(rs.getDouble(6));
				abean.setParamUnit(rs.getString(7));
				
				aList.add(abean);
			}
		}catch(Exception e){
			
		}
		finally {
			DBUtil.close(rs, stmt, conn);
		}

		return aList;
	}


	public static ArrayList<ThresholdBean> updateThresholds(ArrayList<ThresholdBean> aList) throws SQLException {
		String query = "UPDATE THRESHOLD_PARAM SET PARAM_MIN=?, PARAM_UNHEALTHY=?, PARAM_CRITICAL=?, PARAM_MAX=? WHERE PARAM_ID=? ";
		Connection conn = null;
		PreparedStatement stmt = null;
		boolean flag = false;
		
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
			
			stmt = conn.prepareStatement(query);
			for(ThresholdBean bean:aList){
				stmt.setDouble(1, bean.getMin());
				stmt.setDouble(2, bean.getUnhealthy());
				stmt.setDouble(3, bean.getCritical());
				stmt.setDouble(4, bean.getMax());
				stmt.setString(5, bean.getParamID());
				
				stmt.addBatch();
			}
			
			int cnt[] = stmt.executeBatch();
			for(int i=0;i<cnt.length;i++){
				if(cnt[i]!=-2){
					flag = false;
					break;
				}else{
					flag = true;
				}
			}
			
			if(flag) conn.commit();
			else conn.rollback();

		} finally {
			conn.setAutoCommit(true);
			if(stmt!=null) stmt.close();
			if(conn !=null) conn.close();
		}
		return aList;
	}
	
	

	public static ArrayList<BaselineBean> getBaselines() throws SQLException {
		String query = "SELECT DBID, BASELINE_ID, BASELINE_NAME, START_SNAP_ID, START_SNAP_TIME, END_SNAP_ID, END_SNAP_TIME FROM DBA_HIST_BASELINE" +
				" ORDER BY BASELINE_NAME";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<BaselineBean> aList = new ArrayList<BaselineBean>();

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();

			while(rs.next()) {
				BaselineBean abean = new BaselineBean();
				
				abean.setDBID(rs.getString(1));
				abean.setBASELINE_ID(rs.getString(2));
				abean.setBASELINE_NAME(rs.getString(3));
				abean.setSTART_SNAP_ID(rs.getString(4));
				abean.setSTART_SNAP_TIME(rs.getString(5));
				abean.setEND_SNAP_ID(rs.getString(6));
				abean.setEND_SNAP_TIME(rs.getString(7));
				
				aList.add(abean);
			}
		}catch(Exception e){
			
		}
		finally {
			DBUtil.close(rs, stmt, conn);
		}

		return aList;
	}
	
	
	public static void createBaselines(String startID,String endID,String baselineNM) throws SQLException {
		String query = QueryUtil.CREATE_BASELINE;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			stmt.setInt(1, Integer.parseInt(startID));
			stmt.setInt(2, Integer.parseInt(endID));
			stmt.setString(3, baselineNM);
			
			stmt.executeUpdate();
			
		} 		
		finally {
			DBUtil.close(rs, stmt, conn);
		}
	}
	
	public static void deleteBaselines(String baselinename) throws SQLException {
		String query = QueryUtil.DELETE_BASELINE;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query); System.out.println(" baselinename "+ baselinename);
			stmt.setString(1, baselinename);
			stmt.executeUpdate();

		} 		
		finally {
			DBUtil.close(rs, stmt, conn);
		}
	}

	public static LinkedHashMap getBaselinesMap(String[] baselineIDs) {
		// TODO Auto-generated method stub
		return null;
	}

	public static ArrayList<String> getSnapshotIDList() throws Exception{
		ArrayList<String> aList = new  ArrayList<String>();
		String query = "SELECT DISTINCT SNAP_ID FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();
			while(rs.next()){
				aList.add(rs.getString(1));				
			}

		} 		
		finally {
			DBUtil.close(rs, stmt, conn);
		}		 
		return aList;
	}
}
